package com.nian.global.utils;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.BeanUtils;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.beans.PropertyDescriptor;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Array;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.sql.Timestamp;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * Created by nian on 2020/11/6.
 */
public class ExcelExporterUtils {

    private static final String TEMPLATE_BASE_PATH = "template/xls/";

    private ExcelExporterUtils() {}

    /**
     * 导出excel 模板
     *
     */
    public static void exportExcelWithTemplate(Object dataObj, String templateName, String downloadFileName, HttpServletResponse response, HttpServletRequest request) throws IOException, IllegalAccessException, InvocationTargetException, NoSuchMethodException  {
        InputStream is = null;
        OutputStream outputStream = null;

        try {
            is = Thread.currentThread().getContextClassLoader().getResourceAsStream(TEMPLATE_BASE_PATH + templateName + ".xls");
            HSSFWorkbook book = createBook(is , dataObj) ;
            outputStream = response.getOutputStream();
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            if (request.getHeader("User-Agent").toUpperCase().indexOf("MSIE") > 0) { // IE 浏览器
                downloadFileName = URLEncoder.encode(downloadFileName , "UTF-8");
            } else { // 其他浏览器
                downloadFileName = new String(downloadFileName.getBytes("UTF-8") , "ISO8859-1");
            }
            response.setHeader("Content-Disposition", "attachment:filename=" + downloadFileName);
            book.write(outputStream);
;        } finally {
            if (is != null) {
                is.close();
            }
            if (outputStream != null) {
                outputStream.close();
            }
        }
    }

    private static HSSFWorkbook createBook(InputStream is, Object dataObj)  throws IOException, IllegalAccessException,InvocationTargetException, NoSuchMethodException {
        HSSFWorkbook book = new HSSFWorkbook(is);
        HSSFSheet sheet = book.getSheetAt(0);
        Map<String, List<HSSFCell>> propertyNameCellListMap = new LinkedHashMap<>();
        String elRegexp = "[$][{][^}]*[}]";
        Pattern pattern = Pattern.compile(elRegexp);
        int maxRow = sheet.getLastRowNum();
        for (int rowIndex = 0; rowIndex <= maxRow; rowIndex++) {
            HSSFRow currentRow = sheet.getRow(rowIndex);
            if (currentRow == null) {
                continue;
            }
            int maxCellNum = currentRow.getLastCellNum();
            for (int colIndex = 0; colIndex < maxCellNum; colIndex++) {
                HSSFCell cell = currentRow.getCell(colIndex);
                if (cell == null) {
                    continue;
                }
                String cellValue = cell.getStringCellValue();
                Matcher matcher = pattern.matcher(cellValue);
                if (matcher.find()) {
                    String rtexp = matcher.group();
                    String propertyName = rtexp.substring(2, rtexp.length() - 1);
                    List<HSSFCell> propertyCellList = propertyNameCellListMap.get(propertyName);
                    if(propertyCellList == null) {
                        propertyCellList = new ArrayList<>();
                        propertyNameCellListMap.put(propertyName, propertyCellList);
                    }
                    propertyCellList.add(cell);
                }

            }
        }
        Set<Map.Entry<String, List<HSSFCell>>> propertyNameCellListEntries = propertyNameCellListMap.entrySet();
        for (Map.Entry<String, List<HSSFCell>> entry : propertyNameCellListEntries) {
            String propertyName = entry.getKey();
            List<HSSFCell> cellList = entry.getValue();
            FieldDesc[] fieldDescs = getFieldDescs(dataObj, propertyName);
            Object retValue = dataObj;
            for (int i = 0; i < fieldDescs.length; i++) {
                if (fieldDescs[i] == null) {
                    retValue = null;
                    break;
                }
                if (fieldDescs[i].isCollection()) {// 集合
                    Collection collectionVal = null;
                    if (fieldDescs[i].getReadMethod() == null) {// collection is
                        // root
                        collectionVal = (Collection) retValue;
                    } else {
                        collectionVal = (Collection) fieldDescs[i].getReadMethod().invoke(retValue, null);
                    }
                    if (collectionVal == null || collectionVal.size() == 0) {
                        retValue = null;
                    } else {
                        processCollection(sheet, cellList, collectionVal, fieldDescs, i, maxRow);
                    }
                    break;
                } else {
                    retValue = fieldDescs[i].getReadMethod().invoke(retValue, null);
                    if (retValue == null) {
                        break;
                    }
                }
            }
            for (HSSFCell cell : cellList) {
                setCellValue(cell, retValue);
            }
        }

        if(maxRow > 2) {
            //将模板中的表尾写到最后一行
            HSSFRow footRow = sheet.getRow(maxRow);
            HSSFRow lastRow = sheet.createRow(sheet.getLastRowNum() + 1);
            for (int i = 0; i < footRow.getLastCellNum(); i++) {
                HSSFCell templateCell = footRow.getCell(i);
                HSSFCell lastRowCell = lastRow.createCell(i);
                lastRowCell.setCellComment(templateCell.getCellComment());
                lastRowCell.setCellType(templateCell.getCellType());
                lastRowCell.setCellValue(templateCell.getStringCellValue());
            }
            //原表尾删除
            sheet.shiftRows(maxRow + 1, sheet.getLastRowNum(), -1);
        }else{
            HSSFRow footRow = sheet.getRow(maxRow);
            HSSFRow beforeRow = sheet.getRow(maxRow-1);
            for (int i = 0; i < footRow.getLastCellNum(); i++) {
                HSSFCell templateCell = footRow.getCell(i);
                if(StringUtils.isNotEmpty(templateCell.getStringCellValue())){
                    templateCell.setCellStyle(beforeRow.getCell(i).getCellStyle());
                }
            }
        }
        return book;
    }

    private static void processCollection(HSSFSheet sheet, List<HSSFCell> propertyNameCellList, Collection collectionVal,
                                          FieldDesc[] fieldDescs, int startIndex, int maxRow) throws IllegalAccessException, InvocationTargetException {
        FieldDesc collectionFieldDesc = fieldDescs[startIndex];
        Integer firstPageSize = collectionFieldDesc.getFirstPageSize();
        Integer otherPageSize = collectionFieldDesc.getOtherPageSize();
        int collectionIndex = 0;
        int currentRowIndex = 0;
        int cellListSize = propertyNameCellList.size();
        int maxDataRow = (collectionVal.size() - 1) / cellListSize + 1;
        int currentDataRow = 0;
        HSSFRow firstPageLastRow = null; // first page last row
        Iterator iterator = collectionVal.iterator();
        while (iterator.hasNext()) {
            currentDataRow++;
            for (int i = 0; i < cellListSize; i++) {
                HSSFCell propertyCell = propertyNameCellList.get(i);
                Object item = null;
                if (iterator.hasNext()) {
                    item = iterator.next();
                }
                int startRowIndex = propertyCell.getRowIndex();
                int colIndex = propertyCell.getColumnIndex();

                HSSFCell pageFirstContentCell = sheet.getRow(startRowIndex).getCell(colIndex);
                HSSFRow hssfRow = sheet.getRow(startRowIndex + currentRowIndex);
                if (hssfRow == null) {
                    hssfRow = sheet.createRow(startRowIndex + currentRowIndex);
                    hssfRow.setHeight(pageFirstContentCell.getRow().getHeight());
                }
                HSSFCell insertCell = hssfRow.getCell(colIndex);
                if (insertCell == null) {
                    insertCell = hssfRow.createCell(colIndex);
                    if (firstPageLastRow != null
                            && (currentDataRow == maxDataRow || (collectionIndex + cellListSize - firstPageSize * cellListSize)
                            % (otherPageSize * cellListSize) < cellListSize)) {// every
                        // page
                        // last
                        // data
                        // row
                        insertCell.setCellStyle(firstPageLastRow.getCell(colIndex).getCellStyle());
                    } else {
                        insertCell.setCellStyle(pageFirstContentCell.getCellStyle());
                    }
                    insertCell.setCellType(pageFirstContentCell.getCellType());
                }
                Object cellVal = item;
                if (item != null) {
                    for (int j = startIndex + 1; j < fieldDescs.length; j++) {
                        if (cellVal == null) {
                            break;
                        }
                        cellVal = fieldDescs[j].getReadMethod().invoke(cellVal, null);
                    }
                }
                setCellValue(insertCell, cellVal);
                collectionIndex++;

                if (firstPageSize != null) {// 分页
                    boolean needPaging = false;
                    if (collectionIndex == firstPageSize * cellListSize) {// first
                        // page
                        if (collectionVal.size() < collectionIndex) {
                            continue;
                        }
                        needPaging = true;
                        firstPageLastRow = hssfRow;
                    } else if ((collectionIndex - firstPageSize * cellListSize) % (otherPageSize * cellListSize) == 0) {// other
                        // page
                        if (collectionVal.size() < collectionIndex) {
                            continue;
                        }
                        needPaging = true;
                    }
                    if (needPaging) {
                        HSSFCell templateTitleCell = sheet.getRow(startRowIndex - 1).getCell(colIndex); // 同一列的上一行为template
                        // title
                        // cell（上一个cell）
                        if (startRowIndex + currentRowIndex + 1 == maxRow) {
                            currentRowIndex ++;
                        }
//                        HSSFRow blankRow = sheet.createRow(startRowIndex + currentRowIndex + 1); // blank
//                                                                                                // row
//                        blankRow.setHeight(templateTitleCell.getRow().getHeight());
//                        currentRowIndex++;
//                        HSSFRow titleRow = sheet.getRow(startRowIndex + currentRowIndex + 1); // title
//                                                                                             // row
//                        if (titleRow == null) {
//                            titleRow = sheet.createRow(startRowIndex + currentRowIndex + 1); // title
//                                                                                            // row
//                            titleRow.setHeight(templateTitleCell.getRow().getHeight());
//                        }
//
//                        for (int j = 0; j < cellListSize; j++) {// insert title
//                                                                // cell
//                            propertyCell = propertyNameCellList.get(j);
//                            colIndex = propertyCell.getColumnIndex();
//                            templateTitleCell = sheet.getRow(startRowIndex - 1).getCell(colIndex);
//                            HSSFCell titleCell = titleRow.getCell(colIndex);
//                            if (titleCell == null) {
//                                titleCell = titleRow.createCell(colIndex);
//                                titleCell.setCellStyle(pageFirstContentCell.getCellStyle());
//                            }
////                            titleCell.setCellComment(templateTitleCell.getCellComment());
//                            titleCell.setCellType(pageFirstContentCell.getCellType());
//                            titleCell.setCellValue(templateTitleCell.getStringCellValue());
//                        }
//                        currentRowIndex++;
                    }
                }
            }
            currentRowIndex++;
        }
    }

    private static void setCellValue(HSSFCell cell, Object val) {
        if (val != null) {
            Class valClz = val.getClass();
            if (valClz == String.class) {
                cell.setCellValue((String) val);
            } else if (valClz == Integer.class || valClz == Long.class || valClz == BigDecimal.class || valClz == Float.class
                    || valClz == Double.class || valClz == int.class || valClz == long.class || valClz == float.class
                    || valClz == double.class) {
                cell.setCellValue(Double.valueOf(val.toString()));
            } else if (valClz == Timestamp.class || valClz == Date.class) {
                cell.setCellValue((Date) val);
            }
        } else {
            cell.setCellType(HSSFCell.CELL_TYPE_BLANK);
            cell.setCellValue("");
        }
    }

    private static FieldDesc[] getFieldDescs(Object dataObj, String propertyName) throws IllegalAccessException, InvocationTargetException,
            NoSuchMethodException {
        if (dataObj == null) {
            return new FieldDesc[0];
        }
        Class objClzz = dataObj.getClass();
        String[] fieldNames = propertyName.split("[.]");
        FieldDesc[] fieldDescs = new FieldDesc[fieldNames.length];
        for (int i = 0; i < fieldNames.length; i++) {
            fieldDescs[i] = new FieldDesc();
            int leftBracketPos = fieldNames[i].indexOf("[");
            if (leftBracketPos >= 0) {// 集合[]
                fieldDescs[i].setCollection(true);
                int rightBracketPos = fieldNames[i].indexOf("]");
                String pageSizeInfo = fieldNames[i].substring(leftBracketPos + 1, rightBracketPos);
                if (!"null".equals(pageSizeInfo) && !"".equals(pageSizeInfo)) {
                    String[] pageSizeArray = pageSizeInfo.split(",");
                    int firstPageSize = Integer.parseInt(pageSizeArray[0]);
                    fieldDescs[i].setFirstPageSize(firstPageSize);
                    if (pageSizeArray.length > 1) {
                        int otherPageSize = Integer.parseInt(pageSizeArray[1]);
                        fieldDescs[i].setOtherPageSize(otherPageSize);
                    } else {
                        fieldDescs[i].setOtherPageSize(firstPageSize);
                    }
                }
                fieldNames[i] = fieldNames[i].substring(0, leftBracketPos);
            }
            if ("".equals(fieldNames[i])) {// 集合为根
                Object firstElement = getCollectionFirstNotNullElement(dataObj);
                if (firstElement != null) {
                    objClzz = firstElement.getClass();
                } else {
                    break;
                }
            } else {
                PropertyDescriptor descriptor = BeanUtils.getPropertyDescriptor(objClzz, fieldNames[i]);
                fieldDescs[i].setReadMethod(descriptor.getReadMethod());
                Class propertyClass = descriptor.getPropertyType();
                if (propertyClass.isArray() || Collection.class.isAssignableFrom(propertyClass)) {// 集合
                    Object tmpObj = dataObj;
                    for (int j = 0; j <= i; j++) {
                        tmpObj = fieldDescs[j].getReadMethod().invoke(tmpObj, null);
                        if (tmpObj == null) {
                            break;
                        }
                    }
                    Object firstElement = getCollectionFirstNotNullElement(tmpObj);
                    if (firstElement != null) {
                        objClzz = firstElement.getClass();
                    } else {
                        break;
                    }
                } else {
                    objClzz = propertyClass;
                }
            }
        }
        return fieldDescs;
    }

    private static Object getCollectionFirstNotNullElement(Object collectionObj) {
        if (collectionObj == null) {
            return null;
        }
        Class collectionClass = collectionObj.getClass();
        Object firstElement = null; // 集合中第一个元素
        if (collectionClass.isArray()) {
            int len = Array.getLength(collectionObj);
            for (int j = 0; j < len && firstElement == null; j++) {// first not
                // null
                firstElement = Array.get(collectionObj, j);
            }
        } else {
            Iterator iterator = ((Collection) collectionObj).iterator();
            while (iterator.hasNext() && firstElement == null) {// first not
                // null
                firstElement = iterator.next();
            }
        }
        return firstElement;
    }

    private static class FieldDesc {
        private boolean isCollection = false; // 是否为集合
        private Integer firstPageSize = null; // 第一页分页大小
        private Integer otherPageSize = null; // 分页大小
        private Method readMethod; // property 对应getter方法

        public FieldDesc() {
            super();
        }

        public FieldDesc(boolean isCollection, Integer otherPageSize, Method readMethod) {
            this.isCollection = isCollection;
            this.otherPageSize = otherPageSize;
            this.readMethod = readMethod;
        }

        public boolean isCollection() {
            return isCollection;
        }

        public void setCollection(boolean isCollection) {
            this.isCollection = isCollection;
        }

        public Integer getFirstPageSize() {
            return firstPageSize;
        }

        public void setFirstPageSize(Integer firstPageSize) {
            this.firstPageSize = firstPageSize;
        }

        public Integer getOtherPageSize() {
            return otherPageSize;
        }

        public void setOtherPageSize(Integer otherPageSize) {
            this.otherPageSize = otherPageSize;
        }

        public Method getReadMethod() {
            return readMethod;
        }

        public void setReadMethod(Method readMethod) {
            this.readMethod = readMethod;
        }

    }


}
